package cn.itigger.generate.core;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;

import cn.itigger.generate.util.ColumnData;
import cn.itigger.generate.util.CommUtil;
import cn.itigger.generate.util.TableConvert;

public class CreateBean {
	private Connection connection = null;
	static String url;
	static String username;
	static String password;
	static String rt = "\r\t";
	String SQLTables = "show tables";
	private String method;
	private String argv;
	static String selectStr = "select ";
	static String from = " from ";

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setMysqlInfo(String url, String username, String password) {
		this.url = url;
		this.username = username;
		this.password = password;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	public List<String> getTables() throws SQLException {
		Connection con = getConnection();
		PreparedStatement ps = con.prepareStatement(SQLTables);
		ResultSet rs = ps.executeQuery();
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			String tableName = rs.getString(1);
			list.add(tableName);
		}
		rs.close();
		ps.close();
		con.close();
		return list;
	}
	
	public List<ColumnData> getColumnDatas(String dataBaseName,String tableName, String aliasName) throws SQLException {
		String SQLColumns = "select column_name ,data_type,column_comment,0,0,character_maximum_length,is_nullable nullable from information_schema.columns where table_name =  '"
				+ tableName + "' " + "and table_schema =  '" + dataBaseName + "'";

		Connection con = getConnection();
		PreparedStatement ps = con.prepareStatement(SQLColumns);
		List<ColumnData> columnList = new ArrayList<ColumnData>();
		ResultSet rs = ps.executeQuery();
		StringBuffer str = new StringBuffer();
		StringBuffer getset = new StringBuffer();
		while (rs.next()) {
			String name = rs.getString(1);
			String type = rs.getString(2);
			String comment = rs.getString(3);
			String precision = rs.getString(4);
			String scale = rs.getString(5);
			String charmaxLength = rs.getString(6) == null ? "" : rs.getString(6);
			String nullable = TableConvert.getNullAble(rs.getString(7));
			type = getType(type, precision, scale);

			ColumnData cd = new ColumnData();
			cd.setColumnName(aliasName+"."+name);
			cd.setColumnNameNoAlias(name);
			cd.setFormatColumnName(CommUtil.formatName(name));
			cd.setDataType(type);
			cd.setColumnType(rs.getString(2));
			cd.setColumnComment(comment);
			cd.setPrecision(precision);
			cd.setScale(scale);
			cd.setCharmaxLength(charmaxLength);
			cd.setNullable(nullable);
			formatFieldClassType(cd);
			columnList.add(cd);
		}
		argv = str.toString();
		method = getset.toString();
		rs.close();
		ps.close();
		con.close();
		return columnList;
	}
	
	
	public String getTableComment(String dataBaseName,String tableName) throws SQLException {
		String SQLColumns = "select TABLE_COMMENT from information_schema.`TABLES` where TABLE_NAME =  '"
				+ tableName + "' " + "and table_schema =  '" + dataBaseName + "'";

		Connection con = getConnection();
		PreparedStatement ps = con.prepareStatement(SQLColumns);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			return rs.getString(1);
		}
		rs.close();
		ps.close();
		con.close();
		return null;
	}
	
	
	public Map<String, String> getBeanFeilds(String dataBaseName,String tableName,String aliasName,String basePackage) throws SQLException {
		
		Map<String, String> data = new HashMap<String, String>();
		
		String imports = "";
		
		Set<String> importsSet = new HashSet<String>();
		
		
		List<ColumnData> dataList = getColumnDatas(dataBaseName,tableName,aliasName);
		StringBuffer str = new StringBuffer();
		StringBuffer getset = new StringBuffer();
		for (ColumnData d : dataList) {
			String name = d.getFormatColumnName();
			String type = d.getDataType();
			String comment = d.getColumnComment();

			String maxChar = name.substring(0, 1).toUpperCase();
			
			if(comment.indexOf("(")>=0){
				String desc = comment.substring(comment.lastIndexOf("("),comment.length());
				comment = comment.replace(desc, "");
				str.append("\r\t").append("@FieldDesc(name=\""+comment+"\",desc=\""+desc+"\")");
			}else{
				str.append("\r\t").append("@FieldDesc(name=\""+comment+"\")");
			}
			
			
			if(type.contains("Date")){
				importsSet.add("\rimport com.alibaba.fastjson.annotation.JSONField;");
				importsSet.add("\rimport java.util.Date;");
				str.append("\r\t").append("@JSONField(format=\"yyyy/MM/dd HH:mm:ss\")");
			}
			str.append("\r\t").append("private ").append(type + " ").append(name).append(";\r\t");
			String method = maxChar + name.substring(1, name.length());
			getset.append("\r\t").append("public ").append(type + " ").append("get" + method + "() {\r\t");
			getset.append("    return this.").append(name).append(";\r\t}");
			getset.append("\r\t").append("public void ").append("set" + method + "(" + type + " " + name + ") {\r\t");
			getset.append("    this." + name + "=").append(name).append(";\r\t}");
		}
		
		importsSet.add("\rimport cn.itigger.commons.document.FieldDesc;");
		
		argv = str.toString();
		this.method = getset.toString();
		
		for(String s : importsSet){
			imports += s;
		}
		
		data.put("feilds", argv + this.method);
		data.put("imports", imports);
		
		return data;
	}

	private String formatTableName(String name) {
		String[] split = name.split("_");
		if (split.length > 1) {
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < split.length; i++) {
				String tempName = split[i].substring(0, 1).toUpperCase() + split[i].substring(1, split[i].length());
				sb.append(tempName);
			}

			return sb.toString();
		}
		String tempName = split[0].substring(0, 1).toUpperCase() + split[0].substring(1, split[0].length());
		return tempName;
	}

	private void formatFieldClassType(ColumnData columnt) {
		String fieldType = columnt.getColumnType();
		String scale = columnt.getScale();

		if ("N".equals(columnt.getNullable())) {
			columnt.setOptionType("required:true");
		}
		if (("datetime".equals(fieldType)) || ("time".equals(fieldType))) {
			columnt.setClassType("easyui-datetimebox");
		} else if ("date".equals(fieldType)) {
			columnt.setClassType("easyui-datebox");
		} else if ("int".equals(fieldType)) {
			columnt.setClassType("easyui-numberbox");
		} else if ("number".equals(fieldType)) {
			if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0)) {
				columnt.setClassType("easyui-numberbox");
				if (StringUtils.isNotBlank(columnt.getOptionType()))
					columnt.setOptionType(columnt.getOptionType() + "," + "precision:2,groupSeparator:','");
				else
					columnt.setOptionType("precision:2,groupSeparator:','");
			} else {
				columnt.setClassType("easyui-numberbox");
			}
		} else if (("float".equals(fieldType)) || ("double".equals(fieldType)) || ("decimal".equals(fieldType))) {
			columnt.setClassType("easyui-numberbox");
			if (StringUtils.isNotBlank(columnt.getOptionType()))
				columnt.setOptionType(columnt.getOptionType() + "," + "precision:2,groupSeparator:','");
			else
				columnt.setOptionType("precision:2,groupSeparator:','");
		} else {
			columnt.setClassType("easyui-validatebox");
		}
	}

	public String getType(String dataType, String precision, String scale) {
		dataType = dataType.toLowerCase();
		if (dataType.contains("char")||dataType.contains("text")||dataType.contains("mediumblob"))
			dataType = "String";
		else if (dataType.contains("bit"))
			dataType = "Boolean";
		else if (dataType.contains("bigint"))
			dataType = "Long";
		else if (dataType.contains("int"))
			dataType = "Integer";
		else if (dataType.contains("float"))
			dataType = "Float";
		else if (dataType.contains("double"))
			dataType = "Double";
		else if (dataType.contains("number")) {
			if ((StringUtils.isNotBlank(scale)) && (Integer.parseInt(scale) > 0))
				dataType = "java.math.BigDecimal";
			else if ((StringUtils.isNotBlank(precision)) && (Integer.parseInt(precision) > 6))
				dataType = "Long";
			else
				dataType = "Integer";
		} else if (dataType.contains("decimal"))
			dataType = "Double";
		else if (dataType.contains("date"))
			dataType = "Date";
		else if (dataType.contains("time"))
			dataType = "Timestamp";
		else {
			dataType = "Object";
		}
		return dataType;
	}

	public void getPackage(int type, String createPath, String content, String packageName, String className, String extendsClassName, String[] importName) throws Exception {
		if (packageName == null) {
			packageName = "";
		}
		StringBuffer sb = new StringBuffer();
		sb.append("package ").append(packageName).append(";\r");
		sb.append("\r");
		for (int i = 0; i < importName.length; i++) {
			sb.append("import ").append(importName[i]).append(";\r");
		}
		sb.append("\r");
		sb.append("/**\r *  entity. @author wolf Date:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + "\r */");
		sb.append("\r");
		sb.append("\rpublic class ").append(className);
		if (extendsClassName != null) {
			sb.append(" extends ").append(extendsClassName);
		}
		if (type == 1)
			sb.append(" ").append("implements java.io.Serializable {\r");
		else {
			sb.append(" {\r");
		}
		sb.append("\r\t");
		sb.append("private static final long serialVersionUID = 1L;\r\t");
		String temp = className.substring(0, 1).toLowerCase();
		temp = temp + className.substring(1, className.length());
		if (type == 1) {
			sb.append("private " + className + " " + temp + "; // entity ");
		}
		sb.append(content);
		sb.append("\r}");
		System.out.println(sb.toString());
		createFile(createPath, "", sb.toString());
	}

	public String getTablesNameToClassName(String tableName) {
		String tempTables = formatTableName(tableName);
		return tempTables;
	}

	public void createFile(String path, String fileName, String str) throws IOException {
		FileWriter writer = new FileWriter(new File(path + fileName));
		writer.write(new String(str.getBytes("utf-8")));
		writer.flush();
		writer.close();
	}

	public Map<String, Object> getAutoCreateSql(String dataBaseName,String tableName,String aliasName) throws Exception {
		Map<String,Object> sqlMap = new HashMap<String,Object>();
		List<ColumnData> columnDatas = getColumnDatas(dataBaseName,tableName,aliasName);
		String[] columnList = getColumnList(columnDatas); //表字段列表
		String[] columnListNoAlias = getColumnListNoAlias(columnDatas); //表字段列表(不带别名)
		String[] formatColumnList = getFormatColumnList(columnDatas); //实体字段列表
		
		
		String insert = "insert into " + tableName + "(" + StringUtils.join(columnListNoAlias,",") + ")\n values(#{" + StringUtils.join(formatColumnList,"},#{") + "})";
		String delete = getDeleteSql(tableName, columnDatas.get(0));

		tableName = tableName+" "+aliasName;
		String update = getUpdateSql(tableName,columnDatas );
		String updateSelective = getUpdateSelectiveSql(tableName, columnDatas);
		String selectById = getSelectByIdSql(tableName,columnDatas.get(0));
		sqlMap.put("columnList", columnList);
		sqlMap.put("columnFields", getColumnFields(columnDatas));
		sqlMap.put("insert", insert.replace("#{createTime}", "now()").replace("#{createtime}", "now()"));
		sqlMap.put("update", update.replace("#{updatetime}", "now()"));
		sqlMap.put("delete", delete);
		sqlMap.put("updateSelective", updateSelective);
		sqlMap.put("selectById", selectById);
		return sqlMap;
	}
	
	public String[] getColumnList(List<ColumnData> columnDatas) throws SQLException {
		String[] columnList = new String[columnDatas.size()];
		for(int i = 0; i < columnDatas.size(); i++){
			columnList[i] = columnDatas.get(i).getColumnName();
		}
		return columnList;
	}

	public String[] getColumnListNoAlias(List<ColumnData> columnDatas) throws SQLException {
		String[] columnListNoAlias = new String[columnDatas.size()-1];
		for(int i = 1; i < columnDatas.size(); i++){
			columnListNoAlias[i-1] = columnDatas.get(i).getColumnNameNoAlias();
		}
		return columnListNoAlias;
	}

	public String[] getFormatColumnList(List<ColumnData> columnDatas) throws SQLException {
		String[] formatColumnList = new String[columnDatas.size()-1];
		for(int i = 1; i < columnDatas.size(); i++){
			formatColumnList[i-1] = columnDatas.get(i).getFormatColumnName();
		}
		return formatColumnList;
	}
	
	public String getColumnFields(List<ColumnData> columnDatas){
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < columnDatas.size(); i++) {
			sb.append(columnDatas.get(i).getColumnName()).append(" as ").append(columnDatas.get(i).getFormatColumnName());
			if (i + 1 < columnDatas.size())
				sb.append(",");
		}
		return sb.toString();
	}

	public String getDeleteSql(String tableName, ColumnData columnData) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("delete ");
		sb.append("\t from ").append(tableName).append(" where ");
		sb.append(columnData.getColumnNameNoAlias()).append(" = #{").append(columnData.getFormatColumnName()).append("}");
		return sb.toString();
	}

	public String getSelectByIdSql(String tableName, ColumnData columnData) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("select <include refid=\"Base_Column_List\" /> \n");
		sb.append("\t from ").append(tableName).append(" where ");
		sb.append(columnData.getColumnName()).append(" = #{").append(columnData.getFormatColumnName()).append("}");
		return sb.toString();
	}

	public String getUpdateSql(String tableName, List<ColumnData> columnDatas) throws SQLException {
		StringBuffer sb = new StringBuffer();

		for (int i = 1; i < columnDatas.size(); i++) {
			sb.append(columnDatas.get(i).getColumnName() + "=#{" + columnDatas.get(i).getFormatColumnName() + "}");
			if (i + 1 < columnDatas.size())
				sb.append(",");
		}
		String update = "update " + tableName + " set " + sb.toString() + " where " + columnDatas.get(0).getColumnName() + "=#{" + columnDatas.get(0).getFormatColumnName() + "}";
		return update;
	}

	public String getUpdateSelectiveSql(String tableName, List<ColumnData> columnList) throws SQLException {
		StringBuffer sb = new StringBuffer();
		ColumnData cd = (ColumnData) columnList.get(0);
		sb.append("\t<trim  suffixOverrides=\",\" >\n");
		for (int i = 1; i < columnList.size(); i++) {
			ColumnData data = (ColumnData) columnList.get(i);
			sb.append("\t<if test=\"").append(data.getFormatColumnName()).append(" != null ");

			/*if ("String" == data.getDataType()) {
				sb.append(" and ").append(data.getFormatColumnName()).append(" != ''");
			}*/
			sb.append(" \">\n\t\t");
			sb.append(data.getColumnName() + "=#{" + data.getFormatColumnName() + "},\n");
			sb.append("\t</if>\n");
		}
		sb.append("\t</trim>");
		String update = "update " + tableName + " set \n" + sb.toString() + " where " + cd.getColumnName() + "=#{" + cd.getFormatColumnName() + "}";
		return update;
	}
}